import numpy as np
import pandas as pd

random_scores = np.random.randint(40, 101, size=(10, 5))
stus=[f"同学{i}" for i in range(10)]
lessons=['语文','数学','英语','物理','化学']
scores=pd.DataFrame(data=random_scores,index=stus,columns=lessons)
scores.index = [f"同学_{i}" for i in range(1,11)]

data=pd.read_excel('./成绩.xlsx')
data = data.drop(['序号','学籍号','学生属性'],axis=1).set_index('准考证号',drop=True)
print(data['姓名'][52076585])
data_part=data.loc[:,['班级','姓名','语文','数学','英语','物理','化学']]
# 增加总分列
data_part['总分'] = data_part[['语文', '数学', '英语', '物理', '化学']].sum(axis=1)
data_part=data_part.sort_values(by=['总分','语文','数学'],ascending=False)
#print(data_part[data_part['总分']>500][data_part['语文']<130])
#print(data_part[(data_part['总分']>500) & (data_part['语文']<130)])
#print(data_part.query("总分>500 & 语文<130"))

# 计算语文、数学、英语、物理、化学的平均分
subjects = ['语文', '数学', '英语', '物理', '化学']
average_scores = data_part[subjects].mean()
print("各学科平均分：")
print(average_scores)
print(data_part.describe())

# for d in data_part.values:
#     print(d)
# print(data_part)